package com.ybb.dao;

import com.mysql.jdbc.StringUtils;
import com.ybb.pojo.Provider;
import com.ybb.pojo.User;
import com.ybb.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProviderDaoImpl implements ProviderDao {

    @Override
    public List<Provider> getProviderList(String proCode, String proName) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Provider> providerList = new ArrayList<Provider>();

        try {
            connection = JdbcUtil.getConnection();
            String sql = "SELECT id,proCode, proName, proContact, proPhone, proFax, creationDate " +
                    "FROM smbms_provider WHERE 1 = 1 ";
            List<Object> params = new ArrayList<Object>();

            if (!StringUtils.isNullOrEmpty(proCode)) {
                sql += "AND proCode LIKE CONCAT('%', ?, '%') ";
                params.add(proCode);
            }

            if (!StringUtils.isNullOrEmpty(proName)) {
                sql += "AND proName LIKE CONCAT('%', ?, '%') ";
                params.add(proName);
            }

            pstmt = connection.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();
            while (rs.next()) {
                Provider provider = new Provider();
                provider.setId(rs.getLong("id")); // 设置 id
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProFax(rs.getString("proFax"));
                provider.setCreationDate(rs.getDate("creationDate"));
                providerList.add(provider);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(rs, pstmt, connection); // 关闭资源（与你之前的 UserDaoImpl 一致）
        }

        return providerList;
    }

    @Override
    public int getProviderCount(String proCode, String proName) {
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int count = 0;

        try {
            connection = JdbcUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM smbms_provider WHERE 1 = 1 ";
            List<Object> params = new ArrayList<Object>();

            if (!StringUtils.isNullOrEmpty(proCode)) {
                sql += "AND proCode LIKE CONCAT('%', ?, '%') ";
                params.add(proCode);
            }

            if (!StringUtils.isNullOrEmpty(proName)) {
                sql += "AND proName LIKE CONCAT('%', ?, '%') ";
                params.add(proName);
            }

            pstmt = connection.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(rs, pstmt, connection);
        }

        return count;
    }

    @Override
    public Provider getProviderByUid(long uid) {
        Connection connection = JdbcUtil.getConnection();
        Provider provider = null;
        if (connection != null) {
            String sql = "SELECT " +
                    "smbms_provider.id, " +
                    "smbms_provider.proCode, " +
                    "smbms_provider.proName, " +
                    "smbms_provider.proContact, " +
                    "smbms_provider.proPhone, " +
                    "smbms_provider.proFax, " +
                    "smbms_provider.creationDate " +
                    "FROM smbms_provider " +
                    "WHERE smbms_provider.id = ?";

            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                rs = JdbcUtil.query(preparedStatement, new Object[]{uid});
                if (rs.next()) {
                    provider = new Provider();
                    provider.setId(uid);
                    provider.setProCode(rs.getString("proCode"));
                    provider.setProName(rs.getString("proName"));
                    provider.setProContact(rs.getString("proContact"));
                    provider.setProPhone(rs.getString("proPhone"));
                    provider.setProFax(rs.getString("proFax"));
                    provider.setCreationDate(rs.getDate("creationDate"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(rs, preparedStatement, connection);
            }
        }
        return provider;
    }

    @Override
    public int upDateProvider(Provider provider) {

        Connection connection = JdbcUtil.getConnection();
        int updateRows = 0;
        if (connection != null) {
            String sql = "update smbms_provider set proCode=?,proName=?,proContact=?,proPhone=?,proFax=? where id=?";
            List<Object> params = new ArrayList<Object>();
            params.add(provider.getProCode());
            params.add(provider.getProName());
            params.add(provider.getProContact());
            params.add(provider.getProPhone());
            params.add(provider.getProFax());
            params.add(provider.getId());
            PreparedStatement preparedStatement = null;

            try {
                preparedStatement = connection.prepareStatement(sql);
                updateRows = JdbcUtil.update(preparedStatement, params.toArray());

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(null, preparedStatement, connection);
            }

        }
        return updateRows;
    }

    @Override
    public int deleteProvider(Long proid) {
        Connection connection = JdbcUtil.getConnection();
        int deleteRows = 0;
        if (connection != null) {
            String sql = "delete from smbms_provider where id = ?";
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                deleteRows = JdbcUtil.update(preparedStatement, new Object[]{proid});
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(null, preparedStatement, connection);
            }
        }
        return deleteRows;
    }


    @Override
    public boolean isExist(Long proid) {
        Connection connection = JdbcUtil.getConnection();

        if (connection != null) {
            String sql = "select * from smbms_provider where id = ?";
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
                preparedStatement = connection.prepareStatement(sql);
                resultSet = JdbcUtil.query(preparedStatement, new Object[]{proid});
                if (resultSet.next()) {
                    return true;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(null, preparedStatement, connection);
            }
        }
        return false;
    }


    @Override
    public int insertProvider(Provider provider) {
        Connection connection = JdbcUtil.getConnection();
        int insertRows=0;
        if (connection != null) {
            String sql = "insert into smbms_provider(proCode,proName,proDesc,proContact,proPhone,proAddress,proFax,creationDate)" +
                    "values(?,?,?,?,?,?,?,?) ";
            ArrayList<Object> params = new ArrayList<Object>();
            params.add(provider.getProCode());
            params.add(provider.getProName());
            params.add(provider.getProDesc());
            params.add(provider.getProContact());
            params.add(provider.getProPhone());
            params.add(provider.getProAddress());
            params.add(provider.getProFax());
            params.add(provider.getCreationDate());



            PreparedStatement preparedStatement = null;

            try {
                preparedStatement = connection.prepareStatement(sql);
                insertRows = JdbcUtil.update(preparedStatement,params.toArray());

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JdbcUtil.close(null, preparedStatement, connection);
            }

        }
        return insertRows;
    }
}